﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data;
namespace DAO
{
    public class HOCSINHDAO
    {
        QUANLY_DIEMDataContext DB = new QUANLY_DIEMDataContext();
        NAMHOCDAO namdao = new NAMHOCDAO();

        public List<HOCSINHDTO> dslop(int malop)
        {
            try
            {
                List<HOCSINHDTO> ds_hs = new List<HOCSINHDTO>();
                var ds = from p in DB.HOCSINHs join c in DB.CHITIETHOCSINHs on p.MAHS equals c.MAHS where c.MALOP == malop && p.TINHTRANG == 1 && c.MANAMHOC == namdao.LoadNamHocHienTai().MANAMHOC select p ;
                foreach (var child in ds)
                {
                    HOCSINHDTO hs = new HOCSINHDTO();
                    hs.MAHS = child.MAHS;
                    hs.TENHS = child.TENHS;
                    hs.GIOITINH = child.GIOITINH;
                    hs.DIACHI = child.DIACHI;
                    hs.NGAYSINH = child.NGAYSINH;
                    hs.SODIENTHOAIPHUHUYNH = child.SODIENTHOAIPHUHUYNH;
                    hs.ANH = child.ANH;
                    ds_hs.Add(hs);
                }
                return ds_hs;
            }
            catch {
                return null;
            }
        }
        public HOCSINHDTO lay_hocsinh(int mahs)
        {
            try
            {
                HOCSINH hocsinh = DB.HOCSINHs.Where(p=>p.MAHS==mahs).Single<HOCSINH>();
                HOCSINHDTO hs = new HOCSINHDTO();
                hs.MAHS = hocsinh.MAHS;
                hs.TENHS = hocsinh.TENHS;
                hs.GIOITINH = hocsinh.GIOITINH;
                hs.DIACHI = hocsinh.DIACHI;
                hs.NGAYSINH = hocsinh.NGAYSINH;
                hs.SODIENTHOAIPHUHUYNH = hocsinh.SODIENTHOAIPHUHUYNH;
                hs.ANH = hocsinh.ANH;               
                return hs;
            }
            catch
            {
                return null;
            }
        }
        public int them_hs(HOCSINHDTO hs,int malop)
        {
                HOCSINH hocsinh = new HOCSINH();
                hocsinh.TENHS = hs.TENHS;
                hocsinh.NGAYSINH = hs.NGAYSINH;
                hocsinh.DIACHI = hs.DIACHI;
                hocsinh.GIOITINH = hs.GIOITINH;
                hocsinh.TINHTRANG = hs.TINHTRANG;
                hocsinh.SODIENTHOAIPHUHUYNH = hs.SODIENTHOAIPHUHUYNH;
                hocsinh.ANH = hs.ANH;
                try
                {
                    List<int> check = (from d in DB.HOCSINHs where d.GIOITINH == hs.GIOITINH && d.TENHS == hs.TENHS && d.NGAYSINH == hs.NGAYSINH select d.MAHS).ToList();

                    if (check.Count < 1)
                    {
                        DB.HOCSINHs.InsertOnSubmit(hocsinh);
                        DB.SubmitChanges();
                        int mahs = (from d in DB.HOCSINHs where d.TENHS == hs.TENHS && d.NGAYSINH == hs.NGAYSINH && d.GIOITINH == hs.GIOITINH select d.MAHS).Single();

                        CHITIETHOCSINH ct = new CHITIETHOCSINH();
                        ct.MAHS = mahs;
                        ct.MALOP = malop;
                        ct.MANAMHOC = namdao.LoadNamHocHienTai().MANAMHOC;
                        DB.CHITIETHOCSINHs.InsertOnSubmit(ct);
                        DB.SubmitChanges();
                        return 1;
                    }
                    else
                        return 2;
                }
                catch
                {
                    return 0;
                } 
        }
        public int xoa_hs(int mahs)
        {
            try
            {
                HOCSINH hs = DB.HOCSINHs.Single<HOCSINH>(p => p.MAHS == mahs);
                hs.TINHTRANG = 0;
                DB.SubmitChanges();
                return 1;
            }
            catch {
                return 0;
            }
        }
        public int chinhsua_hs(HOCSINHDTO hs,int malop_cu,int malop)
        {
            try
            {
                HOCSINH hocsinh = DB.HOCSINHs.Single<HOCSINH>(p=>p.MAHS==hs.MAHS);
                hocsinh.TENHS = hs.TENHS;
                hocsinh.NGAYSINH = hs.NGAYSINH;
                hocsinh.GIOITINH = hs.GIOITINH;
                hocsinh.DIACHI = hs.DIACHI;
                hocsinh.TINHTRANG = 1;
                hocsinh.SODIENTHOAIPHUHUYNH = hs.SODIENTHOAIPHUHUYNH;
                hocsinh.ANH = hs.ANH;
                DB.SubmitChanges();
                
                CHITIETHOCSINH ctxoa = DB.CHITIETHOCSINHs.Single<CHITIETHOCSINH>(c => c.MAHS == hs.MAHS && c.MALOP == malop_cu);
                DB.CHITIETHOCSINHs.DeleteOnSubmit(ctxoa);
                DB.SubmitChanges();

                CHITIETHOCSINH ct = new CHITIETHOCSINH();              
                ct.MALOP = malop;
                ct.MAHS = hs.MAHS;
                ct.MANAMHOC = namdao.LoadNamHocHienTai().MANAMHOC;
                DB.CHITIETHOCSINHs.InsertOnSubmit(ct);
                DB.SubmitChanges();
                return 1;
            }
            catch (Exception e){
                string s = e.ToString();
                return 0;
            }
            
        }
        //tim hoc sinh
        public List<HOCSINHDTO> TimhocSinh(string tenhs)
        {
            try
            {
                List<HOCSINHDTO> dshs = new List<HOCSINHDTO>();
                var ds = from p in DB.HOCSINHs where p.TENHS.Contains(tenhs) select p;
                foreach (var child in ds)
                {
                    HOCSINHDTO hs = new HOCSINHDTO();
                    hs.MAHS = child.MAHS;
                    hs.TENHS = child.TENHS;
                    hs.NGAYSINH = child.NGAYSINH;
                    hs.GIOITINH = child.GIOITINH;
                    hs.DIACHI = child.DIACHI;
                    hs.ANH = child.ANH;
                    hs.SODIENTHOAIPHUHUYNH = child.SODIENTHOAIPHUHUYNH;
                    dshs.Add(hs);
                }
                return dshs;
            }
            catch
            {
                return null;
            }
        }
        //Xếp Loại học sinh
        public DataTable DanhSachXepLoai(int malop)
        {
            try
            {
                DataTable dt = new DataTable();
                dt.Columns.Add("tenhs");
                dt.Columns.Add("dtb");
                dt.Columns.Add("xeploai");
                DataRow rw;
                var dshs = from c in DB.CHITIETHOCSINHs
                           join h in DB.HOCSINHs
                           on c.MAHS equals h.MAHS
                           where c.MALOP == malop
                           select new { h.MAHS};
                foreach(var h in dshs)
                {
                    var dsdiem = from d in DB.DIEMs 
                                 where d.MAHS == h.MAHS
                                 select d;
                    int dem = 0;
                    double diemhe1 = 0, diemhe2 = 0, diemhe3 = 0;
                    var dsmn = from c in DB.MONHOCs
                               select new { c.MAMH };
                    foreach (var r in dsmn)
                    {
                        var dsdiemmieng = from p in DB.DIEMs
                                          where p.MAMH == r.MAMH && p.MALCD == 1 && p.MAHS == h.MAHS 
                                          select new { p.STT, p.DIEM1 };
                        var dsdiem15 = from p in DB.DIEMs
                                       where p.MAMH == r.MAMH && p.MALCD == 2 && p.MAHS == h.MAHS //&& p.MAHK == mahk
                                       select new { p.STT, p.DIEM1 };
                        var dsdiem1 = from p in DB.DIEMs
                                      where p.MAMH == r.MAMH && p.MALCD == 3 && p.MAHS == h.MAHS //&& p.MAHK == mahk
                                      select new { p.STT, p.DIEM1 };
                        var dsdiemthi = from p in DB.DIEMs
                                        where p.MAMH == r.MAMH && p.MALCD == 4 && p.MAHS == h.MAHS// && p.MAHK == mahk
                                        select new { p.STT, p.DIEM1 };
                    }

                }
                return dt;
            }
            catch
            {
                return null;
            }
        }
    }
}
